package jpave.annotator;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Stack;
import java.util.TreeMap;
import java.util.TreeSet;
import java.util.Vector;

import util.Debug.ErrorReport;
import util.methods.HostsCfg;
import jpave.database.CoreDBWrapper;
import assem.DBConn;

public class GO 
{
	// Using the Uniprot annotation and the GO database with PAVE-specific modifications, add 
	// GO tables to the individual project DB. 
	// This only adds the tables for JPave, not the additional view tables used by web pave.
	public static void addJPaveGOTables(CoreDBWrapper sqlObj, String godbName, String assemblyID, String goslim, String slimfile)
	{
		CoreMain.StartTime("Start GO update");
		int NLOOP=100; // CAS 12/22/14 - quite printing every time through loop
		ResultSet rs = null;
		try
		{
			HostsCfg hosts = new HostsCfg();
			DBConn db = hosts.getDBConn(sqlObj.PAVE_db);
			DBConn goDB = hosts.getDBConn(godbName);
			String upTbl = godbName + ".PAVE_Uniprot";
			
			CoreMain.PrtSpMsg(1, "Add Kegg/Pfam/EC...");

			db.executeUpdate("update pja_db_unique_hits set ec='', pfam='', kegg=''");
			db.executeUpdate("update pja_db_unique_hits as uh, " + upTbl + " as u " + 
					" set uh.kegg=u.kegg, uh.pfam=u.pfam,uh.ec=u.ec where u.upid=uh.hitid");
			
			if (!hasAnnotation(db))
			{
				CoreMain.die("GO was specified but there is no annotation!");
			}
			if (!goFixed(goDB)) fixGODB(sqlObj, godbName);
			
			if (!goDB.tableExists("PAVE_Uniprot")) // note, db.tableExists doesn't see this table which is in the other DB
			{
				CoreMain.PrtError("Cannot add GO tables because " + upTbl + " does not exist.");
				return;
			}

			// First the pja_uniprot_go table which maps uniprot to its GOs, including all ancestors
			if (db.tableExists("pja_uniprot_go"))
			{
				db.executeUpdate("drop table pja_uniprot_go");
			}		
			CoreMain.PrtSpMsg(1, "Add UniProt to GO");
			db.executeUpdate("create table pja_uniprot_go (DUHID int, gonum int, direct boolean default 0, " +
					" EC varchar(3) default '', slim boolean default 0, unique(DUHID,gonum),index(gonum))");
			Vector<Integer> upList = new Vector<Integer>();
			rs = db.executeQuery("select DUHID from pja_db_unique_hits");
			while (rs.next())
			{
				upList.add(rs.getInt(1));
			}
			PreparedStatement stIns = db.prepareStatement("insert ignore into pja_uniprot_go (DUHid,gonum, EC, direct,slim) values(?,?,?,1,0)");
			for (int i = upList.size()-1; i >= 0; i--)
			{
				if (i % NLOOP == 0) System.err.print("\t" + i + "           \r");
			
				int duhid = upList.get(i);
				rs = db.executeQuery("select DUHID,go from pja_db_unique_hits as pja " +
						" join " + upTbl + " as u on u.UPid=pja.hitID where pja.duhid=" + duhid);
				while (rs.next())
				{
					stIns.setInt(1, duhid);
					String gostr = rs.getString("go");
					if (gostr == null) continue;
					String[] gos = gostr.split(";");
					for (String go : gos)
					{
						if (go.trim().equals("")) continue;
						String[] f = go.split(":");
						if (f.length == 3 )
						{
							int gonum = Integer.parseInt(f[1].trim());
							stIns.setInt(2, gonum);
							stIns.setString(3, f[2]);
							stIns.execute();
						}
						else
						{
							CoreMain.PrtWarn("No evidence code or bad format for " + go + " on dbHit# " + duhid);
						}
					}			
				}
				rs.close();	
			}
			rs = db.executeQuery("select count(distinct(duhid)) from pja_uniprot_go");
			rs.first();
			int nUP2GO = rs.getInt(1);
			System.err.println("\t" + nUP2GO + " Uniprots have at least one GO");
			
			// We have to add the ancestors too
			CoreMain.PrtSpMsg(1, "Add GO ancestors ");
			// It could be done in one query but break it up for progress
			rs = db.executeQuery("select distinct gonum from pja_uniprot_go order by gonum asc");
			Vector<Integer> gos = new Vector<Integer>();
			String t2tTbl = godbName + ".graph_path";
			while (rs.next())
			{
				gos.add(rs.getInt(1));
			}
			for (int i = gos.size(); i >= 1; i--)
			{
				if (i % NLOOP == 0) System.err.print("\t" + i + "            \r");
		
				int gonum = gos.get(i-1);
				db.executeUpdate("insert ignore into pja_uniprot_go (DUHID,gonum)  " + 
						" select DUHID, t.ancestor from pja_uniprot_go as p join " 
						+ t2tTbl + " as t on t.child=p.gonum where p.gonum=" + gonum); 
				
			}
			rs = db.executeQuery("select count(distinct(gonum)) from pja_uniprot_go");
			rs.first();
			int numGO2UP = rs.getInt(1);
			CoreMain.PrtSpMsg(1, "\t" +  numGO2UP + " total GOs mapped");

			//
			// Now the go_info table, which holds information about each GO that is hit
			//
			CoreMain.PrtSpMsg(1, "Fill GO information table");

			// get gos again since have added
			rs = db.executeQuery("select distinct gonum from pja_uniprot_go order by gonum asc");
			gos.clear();
			while (rs.next())
			{
				gos.add(rs.getInt(1));
			}
			if (db.tableExists("go_info")) {
				db.executeUpdate("drop table go_info");
				CoreMain.PrtWarn("Any GOseq values will be removed. You will need to re-execute GOseq");
			}
			db.executeUpdate("create table go_info (gonum int, descr tinytext, level smallint default 0, " + 
					" nUnitranHit int unsigned default 0, nTotalReads bigint unsigned default 0," +
					" term_type enum('biological_process','cellular_component','molecular_function'), slim boolean default 0, " + 
					"unique(gonum), index(slim) )");
			for (int i = gos.size()-1; i >= 0; i--)
			{	
				int gonum = gos.get(gos.size()-1-i);
				if (i%NLOOP==0) System.err.print("\t" + i + "            \r");
				db.executeUpdate("insert ignore into go_info select t.gonum, t.name, t.level,0,0,t.term_type,0 from " + godbName + ".term as t  " + 
					" join pja_uniprot_go as p on p.gonum=t.gonum where p.gonum=" + gonum);
			}
			rs = db.executeQuery("select count(*) from go_info");
			rs.first();
			int numGO = rs.getInt(1);
			CoreMain.PrtSpMsg(1, "\t" +  numGO + " GOs updated");

			//
			// Now the pja_unitrans_go table that shows the GOs on each unitrans, and the Uniprot linking them having best e-value
			//
			CoreMain.PrtSpMsg(1, "Map GOs to sequences");
			if (db.tableExists("pja_unitrans_go")) db.executeUpdate("drop table pja_unitrans_go");
			db.executeUpdate("create table pja_unitrans_go (CTGID bigint, gonum int, bestDUH int, bestScore double, " +
					"bestEV boolean default 0, bestAN boolean default 0, direct boolean default 0, slim boolean default 0," +
					" unique(CTGID,gonum), index(gonum))");
			// To populate, sort by e_value and use insert ignore so that only the first (lowest) evalue instance gets inserted. 
			// WN: change to go contig by contig to allow progress and because doing it all at once
			// is a big query than can exhaust temp file size
			
			Vector<Integer> ctgs = new Vector<Integer>();
			rs = db.executeQuery("select ctgid from contig");
			while (rs.next())
			{
				ctgs.add(rs.getInt(1));
			}
			
			for (int i = ctgs.size() - 1; i >= 0; i--)
			{
				int ctgid = ctgs.get(i);
				if (i%NLOOP==0) System.err.print("\t" + i + "           \r");
				db.executeUpdate("insert ignore into pja_unitrans_go " +
					" select  p.CTGID, g.gonum, p.DUHID, p.e_value, p.filter_ovbest,p.filter_best, g.direct,0 " +
					" from pja_db_unitrans_hits as p " + //join pja_db_unique_hits as d on d.DUHID=p.DUHID " +
					" join pja_uniprot_go as g on g.DUHID=p.DUHID " + 
					" where p.ctgid = " + ctgid + " order by g.gonum asc, p.e_value asc"); 							
			}
			rs = db.executeQuery("select count(distinct(ctgid)) from pja_unitrans_go");
			rs.first();
			int nCtgWithGO = rs.getInt(1);
			System.err.println("\t" + nCtgWithGO + " sequences have at least one GO");

			//
			// Fill out the nUnitranHit and nTotalReads columns in go_info
			//
			CoreMain.PrtSpMsg(1, "Finalize GO information table");

			for (int i = gos.size()-1; i >= 0; i--)
			{	
				int gonum = gos.get(gos.size()-1-i);
				if (i%NLOOP==0) System.err.print("\t" + i + "            \r");

				db.executeUpdate("update go_info as gi, " +
					"(select ug.gonum as gonum, count(*) as nCtg, sum(numclones) as nReads from contig as ct " +
					" join pja_unitrans_go as ug on ug.CTGID=ct.CTGID where ug.gonum=" + gonum + ") as T " +
					" set gi.nUnitranHit=T.nCtg, gi.nTotalReads=T.nReads " +
					" where T.gonum=gi.gonum and gi.gonum=" + gonum);
			}
			System.err.println("\tGO information table finalized");
			
			CoreMain.PrtSpMsg(1, "Add GO tree");
			if (db.tableExists("pja_gotree")) db.executeUpdate("drop table pja_gotree");
			
			// Should remove goslim and nTotalReads when ready to fix the web part too
			db.executeUpdate("create table pja_gotree (tblidx int unsigned primary key auto_increment, " +
	    			" gonum int unsigned, level smallint unsigned, name tinytext, nTotalReads bigint unsigned default 0," + 
	    			" term_type enum('biological_process','cellular_component','molecular_function')," +
	    			" index(gonum) )");
			db.executeUpdate("insert into pja_gotree select 0,gt.gonum, gt.level, g.descr, 0, g.term_type  " +
	    			" from " + godbName + ".PAVE_gotree as gt join go_info as g on g.gonum=gt.gonum " +
	    			" order by gt.idx asc");
		
			CoreMain.EndTime("Finish GO update");
		}
		catch(Exception e)
		{
			ErrorReport.die(e, "Error adding GO tables");
		}
	}
	private static boolean goFixed(DBConn goDB)
	{
		try{
			return goDB.tableExists("PAVE_gotree");
		}
		catch(Exception e){ErrorReport.die(e, "Error finding go tree in database");}
		return false;
	}
	private static boolean hasAnnotation(DBConn db)
	{
		try{
			return db.tableExists("pja_db_unitrans_hits");
		}
		catch(Exception e){ErrorReport.die(e, "Error finding go uniprot table in database");}
		return false;
	}
	public static void fixGODB(CoreDBWrapper sqlObj, String godbName) 
	{
		try
		{	
			DBConn goDB = connectToGODB(sqlObj.PAVE_host,sqlObj.PAVE_user,sqlObj.PAVE_password,godbName);
			ResultSet rs = null;
			if (goFixed(goDB))
			{
				return;
			}
			System.err.println("Adding helper columns and tables to GO database " + godbName);
			System.err.println("This only needs to be done once for a new GO database");
			System.err.println("Approximate time 10 minutes\n");
			// Add go number column to the term table, so we don't have to use the
			// annoying term.id
			System.err.println("Add gonum to term");
			if (!goDB.tableHasColumn("term", "gonum"))
			{
				goDB.executeUpdate("alter table term add gonum int unsigned default 0");
				goDB.executeUpdate("alter table term add level smallint default 0");
			}
			goDB.executeUpdate("update term set gonum=cast(substring(acc,4) as unsigned integer) " + 
					" where acc like 'GO:%'");
			goDB.executeUpdate("alter table term add index(gonum)");

			// Add gonums to the graph_path table so we can find ancestor GOs easily
			System.err.println("Add gonum to graph_path");
			if (!goDB.tableHasColumn("graph_path", "child"))
			{
				goDB.executeUpdate("alter table graph_path add child int unsigned default 0");
				goDB.executeUpdate("alter table graph_path add ancestor int unsigned default 0");
				goDB.executeUpdate("alter table graph_path add index(child)");
			}
			int is_a_Num = 0;
			int part_of_Num = 0;
			rs = goDB.executeQuery("select id,acc from term where acc in ('is_a','part_of')");
			while (rs.next())
			{
				int id = rs.getInt(1);
				String acc = rs.getString(2);
				if (acc.equals("is_a")) is_a_Num = id;
				else part_of_Num = id;
			}
			// Add the gonums IF 
			// A) both entries are actual GO terms
			// B) both terms have the same major categories (i.e., we are not going to track the cross-listings between the 3 trees)
			// C) the relationship is "is_a", or "part_of".
			// Then gonum searches automatically ignore the other relationships stored in the table.  
			goDB.executeUpdate("update graph_path set child=0,ancestor=0");
			goDB.executeUpdate("update graph_path, term as tchild, term as tanc set graph_path.child=tchild.gonum, " +
			" graph_path.ancestor=tanc.gonum where tchild.id=graph_path.term2_id and tchild.gonum != 0 " + 
			" and tanc.id=graph_path.term1_id and tanc.gonum != 0 and tchild.term_type=tanc.term_type " +  
			"and relationship_type_id in (" + is_a_Num + "," + part_of_Num + ")");

			
			System.err.println("Add gonum to term2term");
			// Add gonums to the term2term table so we can do the depth-first search
			if (!goDB.tableHasColumn("term2term", "child"))
			{
				goDB.executeUpdate("alter table term2term add child int unsigned default 0");
				goDB.executeUpdate("alter table term2term add parent int unsigned default 0");
				goDB.executeUpdate("alter table term2term add index(child)");
				goDB.executeUpdate("alter table term2term add index(parent)");
			}
			goDB.executeUpdate("update term2term, term set term2term.child=term.gonum " +
					" where term.id=term2term.term2_id and term.gonum != 0 " +
					" and relationship_type_id in (" + is_a_Num + "," + part_of_Num + ")");
			goDB.executeUpdate("update term2term, term set term2term.parent=term.gonum " +
			" where term.id=term2term.term1_id and term.gonum != 0 " + 
			" and relationship_type_id in (" + is_a_Num + "," + part_of_Num + ")");

			
			System.err.println("Populate the GO database");
	    		if (goDB.tableExists("PAVE_gotree"))
	    		{
	    			goDB.executeUpdate("drop table PAVE_gotree");    		
	    		}
	    		goDB.executeUpdate("create table PAVE_gotree (idx int unsigned auto_increment primary key, " +
	    			" gonum int unsigned, level smallint unsigned," + 
	    			" index(gonum) )");			
			TreeMap<Integer,TreeSet<Integer>> parent2child = new TreeMap<Integer,TreeSet<Integer>>();
			String sql = "select child,parent from term2term where child != 0 and parent != 0 " +
			" and relationship_type_id in (" + is_a_Num + "," + part_of_Num + ")";
			rs = goDB.executeQuery(sql);
			while (rs.next())
			{
				int child = rs.getInt("child");
				int parent = rs.getInt("parent");
				if (!parent2child.containsKey(parent))
				{
					parent2child.put(parent,new TreeSet<Integer>());
				}
				parent2child.get(parent).add(child);
			}
			rs.close();
			// Now get the top-level nodes and do a depth-first traversal, adding as we go.
			// Note, some gonums appear in multiple places in the "tree", up to 300+ times.
			// E.g., 433 has 55 immediate parents and 384 locations in the tree.
			// The biggest duplications are in the biological_process tree.
			// (Note, since we ignored cross-relationships above, each go is only in one of the three major trees, e.g. biological process). 
			PreparedStatement ps = goDB.prepareStatement("insert into PAVE_gotree (gonum, level) values(?,?)");
			String[] types = {"biological_process","cellular_component","molecular_function"};
			for (String type : types)
			{
				rs = goDB.executeQuery("select gonum from term where name='" + type + "'");
				rs.first();
				int topNum = rs.getInt("gonum");
				int nOrdered = 0;
				
				Stack<Integer> stack = new Stack<Integer>();
				Stack<Integer> curPath = new Stack<Integer>();
				int level = 0;
				stack.push(topNum);
				while (!stack.isEmpty())
				{
					int num = stack.pop();				
					int curSubtreeRoot = (curPath.empty() ? 0 : curPath.lastElement());
					if (curSubtreeRoot == num)
					{
						// We've now traversed this whole subtree. 
						curPath.pop();
						level--;
					}
					else
					{
						// We're still working through the children of the
						// most recent branching node (or we're still at the root node)
						ps.setInt(1, num);
						ps.setInt(2, level);
						ps.execute();
						// CAS 1may15
						if (nOrdered%5000==0) System.err.print(type + " " + nOrdered + "               \r");
						nOrdered++;

						if (parent2child.containsKey(num))
						{
							curPath.push(num);
							stack.push(num);
							stack.addAll(parent2child.get(num));
							level++;
						}
					}
				}
				//ps.executeBatch();
			}
			// Get the GO levels from the tree and add them to the term table for query access. 
			// For GOs at multiple levels (e.g., cell_part) we take the highest level (e.g. prefer 3 over 2). 
			// This fixes the "cell_part" type problem but is otherwise basically arbitrary.
			System.err.println("Add GO level numbers to term");
			if (!goDB.tableHasColumn("term", "level"))
			{
				goDB.executeUpdate("alter table term add level smallint default 0");
			}
			goDB.executeUpdate("update term set level = (select max(level)+1 from PAVE_gotree where gonum=term.gonum)" );
		}
		catch(Exception e)
		{
			ErrorReport.reportError(e, "Fixing go tree");
		}
	}
	public static DBConn connectToGODB(String host, String user, String pass, String godb)
	{		
		DBConn goDB = null;
		try {
			goDB = new DBConn(host, godb, user, pass);
		}
		catch(Exception e) {CoreMain.PrtWarn("Could not connect to GO database " + godb);}
		return goDB;
	}
	/********************
	 * XXX all the slim stuff is obsolete since we now have a better algorithm for trimming
	 */
	public static void addSlims(DBConn db, DBConn goDB, String godbName, String dbSlim, String slimFile)
	{
		try
		{
			if (dbSlim.length() > 0)
			{
				addDBSlims(db,goDB,dbSlim, godbName);
			}
			else if (slimFile.length() > 0)
			{
				loadSlimFile(db,slimFile);
			}
			doMap2Slim(db, goDB,godbName);
		}
		catch(Exception e)
		{
			ErrorReport.reportError(e, "adding Slims to database");
		}
		
	}
	public static void doMap2Slim(DBConn db, DBConn godb, String godbName) throws Exception
	{
		// Create a couple of helper tables to tell us which go map to which slims by the map2slim "first-in-path" method,
		// and to tell which slims are descendants of other slims, so we can ultimately prune these out for each contig
		if (db.tableExists("pja_go2slim"))
		{
			db.executeUpdate("drop table pja_go2slim");
		}		
		db.executeUpdate("create table pja_go2slim (gonum int, slim int, unique(gonum,slim))");
		if (db.tableExists("pja_slim2slim"))
		{
			db.executeUpdate("drop table pja_slim2slim");
		}		
		db.executeUpdate("create table pja_slim2slim (child int, parent int, unique(child,parent))");

		String term = godbName + ".term";
		String gp = godbName  + ".graph_path";
		String tsub = godbName + ".term_subset";
		
		// the slim2slim table is easy to fill out using the graph_path table
		String query = "insert ignore into pja_slim2slim select g1.gonum, g2.gonum  " +
				" from " + gp + " as gp " +
				" join go_info as g1 on gp.child=g1.gonum and g1.slim=1 " +
				" join go_info as g2 on gp.ancestor=g2.gonum and g2.slim=1 where g1.gonum != g2.gonum";
		//System.err.println(query);
		db.executeUpdate(query);

		// Now the go2slim table, which requires walking through the tree and finding the 
		// next slim up from each go, along each path in which it appears.
		// This is the main part of map2slim.
		ResultSet rs = null;
		// First, we have to get all the slims, and the containment relations.
		HashSet<Integer> slims = new HashSet<Integer>();
		rs = db.executeQuery("select gonum from go_info where slim=1");
		while (rs.next()) slims.add(rs.getInt(1));
		
		HashSet<Integer> seen = new HashSet<Integer>();
		PreparedStatement ps = db.prepareStatement("insert ignore into pja_go2slim (gonum,slim) values(?,?)");
		HashMap<Integer,HashSet<Integer>> descendedFrom = new HashMap<Integer,HashSet<Integer>>();
		String[] types = {"biological_process","cellular_component","molecular_function"};
		for (String type : types)
		{
			HashMap<Integer,HashSet<Integer>> map2slim = new HashMap<Integer,HashSet<Integer>>();
			Stack<Integer> curPath = new Stack<Integer>();
			Stack<Integer> curSlim = new Stack<Integer>(); // tracks the previous slim in the curPath
			int prevLevel = 0;
			int prevGO = 0;
			rs = db.executeQuery("select t.gonum, t.level+1, slim,t.tblidx from pja_gotree as t join go_info as g " +
					" on g.gonum=t.gonum where t.term_type='" + type + "' order by tblidx asc");
			while (rs.next())
			{
				int gonum = rs.getInt(1);
				int level = rs.getInt(2);
				boolean slim = rs.getBoolean(3);
				if (curSlim.size()==0 && !slim && level > 1)
				{
					CoreMain.PrtWarn("empty slim stack!! gonum:" + gonum + ", level:" + level);
				}
				if (level > prevLevel+1)
				{
					CoreMain.PrtWarn("Too large level jump " + rs.getInt(4));
				}
				if (level < prevLevel)
				{
					// We're jumping back up the tree, possibly more than one level
					while(prevLevel > level)
					{
						curPath.pop();
						curSlim.pop();
						prevLevel--;
					}
				}
				else if (level > prevLevel)
				{
					// We just went one level deeper - push the *previous* go onto the stack
					// Although note that we start by pushing 0
					curPath.push(prevGO);
					
					// If we just pushed a slim, put it on the slim stack as well, otherwise
					// just duplicate the last entry in the slim stack
					if (slims.contains(prevGO) || prevGO==0)
					{
						curSlim.push(prevGO);
					}
					else
					{
						curSlim.push(curSlim.peek());
					}
					prevLevel = level;
				}
				prevGO = gonum;
				if (true) //!seen.contains(gonum))
				{
					if (!slim) 
					{
						ps.setInt(1,gonum);
						ps.setInt(2,curSlim.peek());
						ps.addBatch();
				
					}
				}
				seen.add(gonum);
			}
			ps.executeBatch();
		}
		ps = db.prepareStatement("delete from pja_go2slim where gonum=? and slim=?");
		Vector<Integer> gos = new Vector<Integer>();
		rs = db.executeQuery("select distinct gonum from pja_go2slim order by gonum asc");
		while (rs.next())
		{
			int gonum = rs.getInt(1);
			gos.add(gonum);
		}
		rs.close();
		int deleted = 0;
		for (int gonum : gos)
		{
			System.err.print(gonum + "         \r");
			rs = db.executeQuery("select gs2.slim from pja_go2slim as gs1 " +
					" join pja_slim2slim as ss on ss.child=gs1.slim " +
					" join pja_go2slim as gs2 on ss.parent=gs2.slim " +
					" where gs1.gonum=" + gonum + " and gs2.gonum=" + gonum);
			while (rs.next())
			{
				int slim = rs.getInt(1);
				ps.setInt(1, gonum);
				ps.setInt(2,slim);
				ps.addBatch();
				deleted++;
				System.err.println("delete:" + gonum + "," + slim);
			}
			rs.close();
			ps.executeBatch();
		}
		ps.close();
		System.err.println("deleted:" + deleted);
	}

	public static void addDBSlims(DBConn db, DBConn goDB, String dbSlim, String godbName) throws Exception
	{
		ResultSet rs =  null;
		// Get the term id of the slim subset
		int sid = 0;
		CoreMain.PrtSpMsg(1, "Add GO slims:" + dbSlim);
		rs = goDB.executeQuery("select id from term where name='" + dbSlim + "' and acc like 'goslim_%' and term_type='subset'");
		if (rs.first())
		{
			sid = rs.getInt(1);
		}
		else
		{
			System.err.println("Can't find goslim '" + dbSlim + "' in GO database " + godbName);
			return;
		}
		// Update the slim entry in go_info table
		String subsetTbl = godbName + ".term_subset";
		String termTbl = godbName + ".term";
		db.executeUpdate("update go_info," + termTbl + "," + subsetTbl + " set go_info.slim=1 " +
				" where " + termTbl + ".gonum=go_info.gonum and " + subsetTbl + ".term_id=" + termTbl + ".id" +
				" and " + subsetTbl + ".subset_id=" + sid); 

		rs = db.executeQuery("select count(*) from go_info where slim=1");
		rs.first();
		int slimsFound = rs.getInt(1);
		rs = goDB.executeQuery("select count(*) from term_subset where subset_id=" + sid);
		rs.first();
		int slimsTotal = rs.getInt(1);
		
		CoreMain.PrtSpMsg(1, slimsFound + " slims used in project, out of " + slimsTotal + " total slim set");
	}
	// Import file which is list of GOs in any comma or space delimited format (line breaks irrelevant)
	// GOs can be just numbers, or start with GO or GO:, can be quoted, etc. 
	public static void loadSlimFile(DBConn db, String slimFile) throws Exception
	{
		File f = new File(slimFile);
		if (!f.isFile())
		{
			System.err.println("Can't find " + slimFile);
			return;
		}
		CoreMain.PrtSpMsg(1, "Load GO slims from " + slimFile);
		BufferedReader br = new BufferedReader(new FileReader(f));
		TreeSet<Integer> gos = new TreeSet<Integer>();
		int nWarns = 0;
		while (br.ready())
		{
			// change commas --> space, and remove all non-numeric chars
			String[] list = br.readLine().replaceAll(","," ").replaceAll("[^\\d]", " ").split("\\s+");
			for (String go : list)
			{
				String go1 = go.trim().replaceAll("^0+", ""); // trim spaces, remove leading zeros
				try
				{
					int gonum = Integer.parseInt(go1);
					if (gos.contains(gonum))
					{
						nWarns++;
						if (nWarns < 10)
						{
							CoreMain.PrtWarn("DUPLICATE GO:" + go);
						}
					}
					else gos.add(gonum);
				}
				catch(Exception e)
				{
					if (go1.length() > 0 )
					{
						nWarns++;
						if (nWarns < 10)
						{
							CoreMain.PrtWarn("BAD GO:" + go);
						}
					}
				}
			}
		}
		if (nWarns >= 10)
		{
			CoreMain.PrtWarn((nWarns-10) + " warning messages suppressed!");
		}
		if (gos.size() == 0)
		{
			CoreMain.PrtWarn("No GOs loaded!");
			return;
		}
		CoreMain.PrtSpMsg(1, "Found " + gos.size() + " GO slims");
		for (int gonum : gos)
		{
			db.executeUpdate("update go_info set slim=1 where gonum=" + gonum);
		}
		ResultSet rs = db.executeQuery("select count(*) from go_info where slim=1");
		rs.first();
		int slimsFound = rs.getInt(1);
		CoreMain.PrtSpMsg(1, slimsFound + " slims used in project, out of " + gos.size() + " total slim set");
	}
}
